package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import entity.OptionInfo;
import entity.QuestionInfo;
import util.DBAccess;

public class QuestionDao {

	// 显示题库中所有题目
	public List<QuestionInfo> getAllQus() {
		return getAllQus(0, Short.MAX_VALUE);
	}

	public List<QuestionInfo> getAllQus(int start, int count) {
		List<QuestionInfo> questions = new ArrayList<QuestionInfo>();
		String sql = "select * from questioninfo where isDelete = 0 order by id limit ?,?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, start);
			ps.setInt(2, count);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				QuestionInfo question = new QuestionInfo();
				question.setqId(rs.getInt(1));
				question.setQuestionContent(rs.getString(2));
				question.setImgUrl(rs.getString(3));
				question.setExplain(rs.getString(4));
				question.setLabel(rs.getString(5));
				question.setQtype(rs.getInt(6));
				question.setQsubject(rs.getInt(7));
				question.setOptions(new QuestionDao().getOptions(rs.getInt(1)));

				questions.add(question);
			}
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return questions;
	}

	// 获取题目总数
	public int getTotal() {
		int total = 0;
		String sql = "select count(*) from questioninfo where isDelete = 0";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				total = rs.getInt(1);
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}

	// 获取题目总数
	public int getExisQus() {
		int total = 0;
		String sql = "select count(*) from questioninfo ";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				total = rs.getInt(1);
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}

	// 根据题号去题库找题目
	public QuestionInfo queryQus(int questionId) {
		QuestionInfo question = new QuestionInfo();
		String sql = "select * from questioninfo where id = ?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, questionId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				question.setqId(rs.getInt(1));
				question.setQuestionContent(rs.getString(2));
				question.setImgUrl(rs.getString(3));
				question.setExplain(rs.getString(4));
				question.setLabel(rs.getString(5));
				question.setQtype(rs.getInt(6));
				question.setQsubject(rs.getInt(7));
				// 根据题目编号，从选项表里拿对应选项然后装给question对象
				List<OptionInfo> options = new QuestionDao().getOptions(rs.getInt(1));
				question.setOptions(options);
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return question;
	}
	
	//根据题号取得正确选项的内容
	public List<String> getAnswerByQusId(int questionId) {
		List<String> answers = new ArrayList<String>();
		String sql = "select optioncontent from optioninfo where qid = ? and isAnswer = 1";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, questionId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String answer = rs.getString(1);
				answers.add(answer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return answers;
	}

	// 根据题目编号从选项表里拿到该题的选项
	public List<OptionInfo> getOptions(int questionId) {
		List<OptionInfo> options = new ArrayList<OptionInfo>();
		String sql = "select * from optioninfo where qid =?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, questionId);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				OptionInfo option = new OptionInfo();
				option.setOptionNum(rs.getString(3));
				option.setqId(questionId);
				option.setOptionContent(rs.getString(4));
				option.setIsAnswer(rs.getInt(5));
				options.add(option);
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return options;
	}

	// 根据科目，从题库表中随机抽题并组卷
	public List<QuestionInfo> extractQusBySub(int subject, String paperId) {
		List<QuestionInfo> questions = new ArrayList<QuestionInfo>();
		String sql = "select * from questioninfo where subject = ? and isDelete = 0 order by rand()  limit 5;";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, subject);
			ResultSet rs = ps.executeQuery();
			int index = 1;
			while (rs.next()) {
				QuestionInfo question = new QuestionInfo();
				int questionId = rs.getInt(1);
				question.setqId(questionId);

				// 每从数据库中拿到一题，就往组卷试题表里插入一次(UserChoice和isCorrect之后更新)
				insertQusToPaper(questionId, paperId, index);
				question.setQuestionContent(rs.getString(2));
				question.setImgUrl(rs.getString(3));
				question.setExplain(rs.getString(4));
				question.setLabel(rs.getString(5));
				question.setQtype(rs.getInt(6));
				question.setQsubject(rs.getInt(7));

				// 根据题目编号，从选项表里拿对应选项然后装给question对象
				List<OptionInfo> options = new QuestionDao().getOptions(rs.getInt(1));
				question.setOptions(options);

				questions.add(question);
				index++;
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return questions;
	}
	
	//为模拟考试抽题（不区分考试题型与标签）
	public List<QuestionInfo> extractQusForExam(String paperId, int subject) {
		List<QuestionInfo> questions = new ArrayList<QuestionInfo>();
		String sql = "select * from questioninfo where isDelete = 0 and subject = ?  order by rand()  limit 20;";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, subject);
			ResultSet rs = ps.executeQuery();
			int index = 1;
			while (rs.next()) {
				QuestionInfo question = new QuestionInfo();
				int questionId = rs.getInt(1);
				question.setqId(questionId);

				// 每从数据库中拿到一题，就往组卷试题表里插入一次(UserChoice和isCorrect之后更新)
				insertQusToPaper(questionId, paperId, index);
				question.setQuestionContent(rs.getString(2));
				question.setImgUrl(rs.getString(3));
				question.setExplain(rs.getString(4));
				question.setLabel(rs.getString(5));
				question.setQtype(rs.getInt(6));
				question.setQsubject(rs.getInt(7));

				// 根据题目编号，从选项表里拿对应选项然后装给question对象
				List<OptionInfo> options = new QuestionDao().getOptions(rs.getInt(1));
				question.setOptions(options);

				questions.add(question);
				index++;
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return questions;
	}

	// 根据题型，从题库表中随机抽题
	public List<QuestionInfo> extractQusByTyp(int qtype, String paperId, int subject) {
		List<QuestionInfo> questions = new ArrayList<QuestionInfo>();
		String sql = "select * from questioninfo where qtype = ? and isDelete = 0 and subject = ?  order by rand()  limit 10;";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, qtype);
			ps.setInt(2, subject);
			ResultSet rs = ps.executeQuery();
			int index = 1;
			while (rs.next()) {
				QuestionInfo question = new QuestionInfo();
				int questionId = rs.getInt(1);
				question.setqId(questionId);

				// 每从数据库中拿到一题，就往组卷试题表里插入一次(UserChoice和isCorrect之后更新)
				insertQusToPaper(questionId, paperId, index);
				question.setQuestionContent(rs.getString(2));
				question.setImgUrl(rs.getString(3));
				question.setExplain(rs.getString(4));
				question.setLabel(rs.getString(5));
				question.setQtype(rs.getInt(6));
				question.setQsubject(rs.getInt(7));

				// 根据题目编号，从选项表里拿对应选项然后装给question对象
				List<OptionInfo> options = new QuestionDao().getOptions(rs.getInt(1));
				question.setOptions(options);

				questions.add(question);
				index++;
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return questions;
	}

	// 根据标签，从题库表中随机抽题
	public List<QuestionInfo> extractQusByLab(String label, String paperId, int subject) {
		List<QuestionInfo> questions = new ArrayList<QuestionInfo>();
		String sql = "select * from questioninfo where label like ? and isDelete = 0 and subject = ?  order by rand()  limit 10;";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setString(1, "%" + label + "%");
			ps.setInt(2, subject);
			ResultSet rs = ps.executeQuery();
			int index = 1;
			while (rs.next()) {
				QuestionInfo question = new QuestionInfo();
				int questionId = rs.getInt(1);
				question.setqId(questionId);

				// 每从数据库中拿到一题，就往组卷试题表里插入一次(UserChoice和isCorrect之后更新)
				insertQusToPaper(questionId, paperId, index);
				question.setQuestionContent(rs.getString(2));
				question.setImgUrl(rs.getString(3));
				question.setExplain(rs.getString(4));
				question.setLabel(rs.getString(5));
				question.setQtype(rs.getInt(6));
				question.setQsubject(rs.getInt(7));

				// 根据题目编号，从选项表里拿对应选项然后装给question对象
				List<OptionInfo> options = new QuestionDao().getOptions(rs.getInt(1));
				question.setOptions(options);

				questions.add(question);
				index++;
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return questions;
	}

	// 抽到题目后，将题目编号和考试号插入组卷试题表
	public void insertQusToPaper(int questionId, String paperId, int index) {
		String sql = "insert into paperquestioninfo values(null,?,?,null,null,?)";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setString(1, paperId);
			ps.setInt(2, questionId);
			ps.setInt(3, index);

			ps.execute();
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 删除题目(将isDelete置为1)
	public void updateIsDelete(int qId) {
		String sql = "update questioninfo set isDelete = 1 where id = ?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, qId);

			ps.execute();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 根据题型(单选,多选,判断)获取题数
	public int getCountByQtype(int qtype) {
		int total = 0;
		String sql = "select count(*) from questioninfo where isDelete = 0 and qtype=?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, qtype);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				total = rs.getInt(1);
			}
			ps.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}

	// 根据题干类型(交规，速度，时间)获取题数
	public int getCountByLabel(String label) {
		int total = 0;

		try (Connection conn = new DBAccess().getConnection(); Statement s = conn.createStatement();) {
			String sql = "select count(*) from questioninfo where label like '%" + label + "%' and isDelete = 0";
			ResultSet rs = s.executeQuery(sql);
			while (rs.next()) {
				total = rs.getInt(1);
			}

			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}

	// 新增题目
	public void addQus(QuestionInfo question) {
		String sql = "insert into questioninfo values(null,?,?,?,?,?,?,0)";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setString(1, question.getQuestionContent());
			ps.setString(2, question.getImgUrl());
			ps.setString(3, question.getExplain());
			ps.setString(4, question.getLabel());
			ps.setInt(5, question.getQtype());
			ps.setInt(6, question.getQsubject());

			ps.execute();

			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	// 更新正确的选项
	public void updateCorrect(int questionId, String optionNum) {
		String sql = "update optioninfo set isAnswer = 1 where qid = ? and optionnum = ?";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, questionId);
			ps.setString(2, optionNum);

			ps.execute();
			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	// 添加选项
	public void addOpt(int questionId, String optionNum, String optionContent) {
		String sql = "insert into optioninfo values (null,?,?,?,?)";
		try (Connection conn = new DBAccess().getConnection(); PreparedStatement ps = conn.prepareStatement(sql);) {
			ps.setInt(1, questionId);
			ps.setString(2, optionNum);
			ps.setString(3, optionContent);
			ps.setInt(4, 0);
			ps.execute();

			ps.close();
			conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	

	// 错题功能
	// public List<QuestionInfo> queryError(String paperId) {
	// List<QuestionInfo> questions = new ArrayList<QuestionInfo>();
	// List<String> ids = new ArrayList<String>();
	// String sql = "select qid from paperquestioninfo where iscorrect = 0";
	// try (Connection conn = new DBAccess().getConnection(); PreparedStatement
	// ps = conn.prepareStatement(sql);) {
	// ResultSet rs = ps.executeQuery();
	// int index = 1;
	// while (rs.next()) {
	//
	// int errorQusId = rs.getInt(1);
	// insertQusToPaper(errorQusId, paperId, index);
	// QuestionInfo question = queryQus(errorQusId);
	// questions.add(question);
	// index++;
	//
	// }
	// ps.close();
	// conn.close();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	// return questions;
	// }

}
